
export const usermap = {
//src_user
  ishave:e=>`
    SELECT COUNT(*) NUM FROM SRC_USER 
    WHERE ${e} = ?`,
  regin:`
    INSERT INTO SRC_USER 
    (ACCOUNT,PASSWORD,EMAIL,TYPE) VALUES (?,?,?,?)`,
  login:e => `
    SELECT ID,PASSWORD,TYPE,EMAIL FROM SRC_USER
    WHERE ${e}= ? AND ISUSE = 1`,
//user_info
  regininfo:`
    INSERT INTO USER_INFO (U_ID,NAME) 
    SELECT ID,? NAME FROM SRC_USER 
    WHERE ACCOUNT = ?`,
  getinfo:`
    SELECT * FROM USER_INFO WHERE U_ID = 
    (SELECT ID FROM SRC_USER 
      WHERE ID = ? AND ACCOUNT = ? AND PASSWORD = ? AND ISUSE = 1 AND TYPE = ?)`,
  /**
   * [{key,value},{key,value}]
   */
  changeinfo: e=>`
    UPDATE user_info SET ${e.map(e=>`${e.key}='${e.value}'`).join(",")} 
    WHERE i_id=? AND u_id=?
    `,
  selectname:`
    SELECT name FROM USER_INFO
    WHERE U_ID = ?`,
  
}


export const elsemap = {
  getadver:`
    select * from else_adver`,
  
}

export const cpttmap = {
  select_all:`
    SELECT * FROM SRC_CPTTS
    WHERE ISUSE = 1`,
  // select_no_over_home:`
  //   SELECT * FROM SRC_CPTTS 
  //   WHERE OVERTIME > SYSDATE()
  //   AND ISUSE = 1
  //   AND id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM SRC_CPTTS)))
  //   LIMIT 0,11
  //   `,
  select_no_over_home:`
    SELECT *,RAND() A FROM SRC_CPTTS 
    WHERE OVERTIME > SYSDATE()
    AND ISUSE = 1
    AND ISSHOW = 1
    ORDER BY A
    LIMIT 0,11
    `,
  select_no_going_home:`
    SELECT *,RAND() A FROM SRC_CPTTS 
    WHERE GOINGTIME > SYSDATE() 
    AND OVERTIME < SYSDATE()
    AND ISUSE = 1
    AND ISSHOW = 1
    ORDER BY A
    LIMIT 0,11`,
  select_delete:`
    SELECT * FROM SRC_CPTTS
    WHERE ISUSE = 0`,
  selectone:`
    SELECT * FROM SRC_CPTTS
    WHERE ID = ?
    `,
  getapplay:`
    SELECT * FROM (
    	SELECT * FROM CPTT_APPLAY  WHERE CPTT_ID = ?
    ) AS A LEFT JOIN (
    	SELECT u_id,name FROM USER_INFO
    ) AS B ON A.USER_ID = B.U_ID`,
  getmyapplay:`
    SELECT id,cptt_id,user_id,u2,u3,applaydata 
    FROM CPTT_APPLAY 
    WHERE USER_ID=?`,
  canjoin:`
    SELECT COUNT(*) num FROM CPTT_APPLAY 
    WHERE CPTT_ID = ? AND KEYCODE = ?
    `,
  getgroupbycode:`
    SELECT id,user_id,applaydata,captain FROM CPTT_APPLAY
    WHERE KEYCODE = ? AND CPTT_ID = ?
    `,
  getkeycode:`
    SELECT KEYCODE FROM CPTT_APPLAY 
    WHERE USER_ID=?
    AND CPTT_ID=?
    `,
  selectavabyid:e=>`
    SELECT u_id,name,ava 
    FROM USER_INFO 
    WHERE U_ID IN (${e.join(",")})
    `,
  joinAteam:`
    INSERT INTO CPTT_APPLAY (CPTT_ID,USER_ID,KEYCODE,CAPTAIN)
    SELECT ?,?,?,?
    WHERE NOT EXISTS (SELECT * FROM CPTT_APPLAY WHERE CPTT_ID=? AND USER_ID=?)`,
  applay:`
    INSERT INTO CPTT_APPLAY (CPTT_ID,USER_ID) 
    SELECT ?,?
    WHERE NOT EXISTS (SELECT * FROM CPTT_APPLAY WHERE CPTT_ID=? AND user_id=?)`
}

export const hiscpttmap = {
  select_ending:`
    SELECT * FROM SRC_HISEVENTS
    WHERE ISUSE = 1`,
}